﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;

namespace ClassLibrary_fqy_NET
{
    /// <summary>
    /// NPOIHelper类操作
    /// </summary>
    public class NPOIHelper
    {

        #region 同步方法
        /// <summary>
        /// 创建新的本地表格
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="sheetName">页名</param>
        /// <returns>创建的数据流</returns>
        public static FileStream? CreateLocalExcel(string bookName, string sheetName = "sheet1")
        {
            IWorkbook wb;
            var extensionName = Path.GetExtension(bookName);
            if (extensionName.Length != 0)
            {
                if (extensionName == ".xls")
                {
                    wb = new HSSFWorkbook();
                }
                else if (extensionName == ".xlsx")
                {
                    wb = new XSSFWorkbook();
                }
                else
                {
                    Path.ChangeExtension(bookName, "xlsx");
                    wb = new XSSFWorkbook();
                }
            }
            else
            {
                Path.ChangeExtension(bookName, ".xlsx");
                wb = new XSSFWorkbook();
            }


            ISheet st = wb.CreateSheet(sheetName);
            using (FileStream fs = File.OpenWrite(bookName))
            {
                wb.Write(fs);
                return fs;
            }
        }
        /// <summary>
        /// 获取本地表格是否存在
        /// </summary>
        /// <param name="path">本地表格绝对路径</param>
        /// <returns> -1：表示未搜索到文件或文件格式错误  0:无异常 </returns>
        public static int ReadLocalExcel(string path)
        {
            if (!File.Exists(path) || (Path.GetExtension(path) != ".xls" && Path.GetExtension(path) != ".xlsx"))
            {
                return -1;
            }

            return 0;
        }
        /// <summary>
        /// 获取表格中的sheet名称
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <returns></returns>
        public static string[] GetSheet(string path)
        {
            IWorkbook? workbook;

            if (!File.Exists(path))
            {
                return null!;
            }

            FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            if (Path.GetExtension(path) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                workbook = null;
            }

            if (workbook != null)
            {
                //获取工作簿的sheet数量
                int sheetNum = workbook.NumberOfSheets;
                if (sheetNum > 0)
                {
                    string[] sheetNames = new string[sheetNum];
                    for (int i = 0; i < sheetNum; i++)
                    {
                        sheetNames[i] = workbook.GetSheetAt(i).SheetName;
                    }
                    return sheetNames;
                }
                else
                {
                    return null!;
                }
            }
            else
            {
                return null!;
            }
        }
        /// <summary>
        /// 获取所选列的所有值
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <param name="sheetname">sheet名称</param>
        /// <param name="cellnum">列索引(从0开始)</param>
        /// <returns></returns>
        public static string[] GetCellValues(string path, string sheetname, int cellnum)
        {
            IWorkbook? workbook;
            ISheet? sheet;

            if (!File.Exists(path))
            {
                return null!;
            }

            FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            if (Path.GetExtension(path) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                workbook = null;
            }

            if (workbook != null)
            {
                //获取工作簿的sheet数量
                int sheetNum = workbook.NumberOfSheets;
                if (sheetNum > 0)
                {
                    sheet = workbook.GetSheet(sheetname);
                    if (sheet != null)
                    {
                        //获取当前sheet的行数（有数据的行数）
                        var rowsNum = sheet.PhysicalNumberOfRows;
                        if (rowsNum > 0)
                        {
                            string[] strCellsValue = new string[rowsNum];
                            //选中标题行
                            IRow row = sheet.GetRow(0);
                            //获取当前sheet的列数（有数据的列数）
                            var CellsNum = row.PhysicalNumberOfCells;
                            if (CellsNum > cellnum)
                            {
                                for (int i = 0; i < rowsNum; i++)
                                {
                                    var cell = sheet.GetRow(i).GetCell(cellnum);
                                    if (cell != null)
                                    {
                                        switch (cell.CellType)
                                        {
                                            case CellType.Unknown:
                                                break;
                                            case CellType.Numeric:
                                                strCellsValue[i] = cell.NumericCellValue.ToString();
                                                break;
                                            case CellType.String:
                                                strCellsValue[i] = cell.StringCellValue;
                                                break;
                                            case CellType.Formula:
                                                break;
                                            case CellType.Blank:
                                                strCellsValue[i] = string.Empty;
                                                break;
                                            case CellType.Boolean:
                                                break;
                                            case CellType.Error:
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                    else
                                    {
                                        strCellsValue[i] = string.Empty;
                                    }
                                }
                                return strCellsValue;
                            }
                            else
                            {
                                return null!;
                            }
                        }
                        else
                        {
                            return null!;
                        }
                    }
                    else
                    {
                        return null!;
                    }
                }
                else
                {
                    return null!;
                }
            }
            else
            {
                return null!;
            }

        }
        /// <summary>
        /// 获取所选行的所有值
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <param name="sheetname">sheet名称</param>
        /// <param name="rownum">行索引(从0开始)</param>
        /// <returns></returns>
        public static string[] GetRowValues(string path, string sheetname, int rownum)
        {
            IWorkbook? workbook;
            ISheet? sheet;

            if (!File.Exists(path))
            {
                return null!;
            }

            FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            if (Path.GetExtension(path) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                workbook = null;
            }

            if (workbook != null)
            {
                //获取工作簿的sheet数量
                int sheetNum = workbook.NumberOfSheets;
                if (sheetNum > 0)
                {
                    sheet = workbook.GetSheet(sheetname);
                    if (sheet != null)
                    {
                        //获取当前sheet的行数（有数据的行数）
                        var rowsNum = sheet.PhysicalNumberOfRows;
                        if (rowsNum > 0)
                        {
                            //选中标题行
                            IRow row = sheet.GetRow(0);
                            //获取当前sheet的列数（有数据的列数）
                            var cellsNum = row.PhysicalNumberOfCells;
                            string[] strRowsValue = new string[cellsNum];
                            
                            if (rowsNum > rownum)
                            {
                                for (int i = 0; i < cellsNum; i++)
                                {
                                    var cell = sheet.GetRow(rownum).GetCell(i);
                                    if (cell != null)
                                    {
                                        switch (cell.CellType)
                                        {
                                            case CellType.Unknown:
                                                break;
                                            case CellType.Numeric:
                                                strRowsValue[i] = cell.NumericCellValue.ToString();
                                                break;
                                            case CellType.String:
                                                strRowsValue[i] = cell.StringCellValue;
                                                break;
                                            case CellType.Formula:
                                                break;
                                            case CellType.Blank:
                                                strRowsValue[i] = string.Empty;
                                                break;
                                            case CellType.Boolean:
                                                break;
                                            case CellType.Error:
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                    else
                                    {
                                        strRowsValue[i] = string.Empty;
                                    }
                                }
                                return strRowsValue;
                            }
                            else
                            {
                                return null!;
                            }
                        }
                        else
                        {
                            return null!;
                        }
                    }
                    else
                    {
                        return null!;
                    }
                }
                else
                {
                    return null!;
                }
            }
            else
            {
                return null!;
            }

        }
        /// <summary>
        /// 向表格中批量插入一行数据(带标题行)
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="cellTitleVlaue">标题行写入值</param>
        /// <param name="cellDataVlaue">数据行写入值</param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static int InsertDataToLocalExcel(string bookName, List<string> cellTitleVlaue, List<string> cellDataVlaue)
        {
            //创建工作簿并实例化
            IWorkbook workbook;
            ISheet sheet;

            if (!File.Exists(bookName))
            {
                return -1;
            }

            FileStream fs = new FileStream(bookName, FileMode.Open, FileAccess.Read);
            if (Path.GetExtension(bookName) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(bookName) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                return -1;
            }

            //判断工作簿中是否有工作表
            if (workbook.NumberOfSheets > 0)
            {
                sheet = workbook.GetSheetAt(0);//选择工作簿中的第一张工作表                           
                //获取当前sheet的行数（有数据的行数）,判断选中的表中是否有数据
                if (sheet.PhysicalNumberOfRows > 0)
                {
                    IRow row = sheet.CreateRow(sheet.PhysicalNumberOfRows + 1);//创建标题行
                    for (int i = 0; i < cellTitleVlaue.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(cellTitleVlaue[i]);
                    }
                    row = sheet.CreateRow(sheet.PhysicalNumberOfRows + 1);//创建数据行
                    for (int i = 0; i < cellDataVlaue.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(cellDataVlaue[i]);
                    }
                }
                else
                {
                    IRow row = sheet.CreateRow(0);//创建标题行
                    for (int i = 0; i < cellTitleVlaue.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(cellTitleVlaue[i]);
                    }
                    row = sheet.CreateRow(1);//创建数据行
                    for (int i = 0; i < cellDataVlaue.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(cellDataVlaue[i]);
                    }
                }

                fs = new FileStream(bookName, FileMode.Open, FileAccess.Write);//配置文件流为写入
                workbook.Write(fs);//向表中写入数据
                fs.Close();//关闭文件
                workbook.Close();//关闭工作簿

                return 0;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        /// 向表格中批量插入一行数据(不带标题行)
        /// </summary>
        /// <param name="bookName"></param>
        /// <param name="cellDataVlaue"></param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static int InsertDataToLocalExcel(string bookName, List<string> cellDataVlaue)
        {
            //创建工作簿并实例化
            IWorkbook workbook;
            ISheet sheet;

            if (!File.Exists(bookName))
            {
                return -1;
            }

            FileStream fs = new FileStream(bookName, FileMode.Open, FileAccess.Read);
            if (Path.GetExtension(bookName) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(bookName) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                return -1;
            }

            //判断工作簿中是否有工作表
            if (workbook.NumberOfSheets > 0)
            {
                sheet = workbook.GetSheetAt(0);//选择工作簿中的第一张工作表                           
                //获取当前sheet的行数（有数据的行数）,判断选中的表中是否有数据
                if (sheet.PhysicalNumberOfRows > 0)
                {
                    IRow row = sheet.CreateRow(sheet.PhysicalNumberOfRows + 1);//创建数据行
                    for (int i = 0; i < cellDataVlaue.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(cellDataVlaue[i]);
                    }

                    fs = new FileStream(bookName, FileMode.Open, FileAccess.Write);//配置文件流为写入
                    workbook.Write(fs);//向表中写入数据
                    fs.Close();//关闭文件
                    workbook.Close();//关闭工作簿

                    return 0;
                }
                else
                {
                    return -1;
                }
            }
            else
            {
                return -1;
            }
        }
        /// 向表格单元格中插入数据（默认操作sheet1）
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="rowNum">单元格行号</param>
        /// <param name="cellNum">单元格列号</param>
        /// <param name="value">要写入的值</param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static int InsertDataToLocalExcelCell(string bookName, int rowNum, int cellNum, string value)
        {
            //创建工作簿并实例化
            IWorkbook wb;

            if (!File.Exists(bookName))
            {
                return -1;
            }

            FileStream fs = new FileStream(bookName, FileMode.Open, FileAccess.Read);
            if (Path.GetExtension(bookName) == ".xls")
            {
                wb = new HSSFWorkbook(fs);
            }
            else if (Path.GetExtension(bookName) == ".xlsx")
            {
                wb = new XSSFWorkbook(fs);
            }
            else
            {
                return -1;
            }
            //配置单元格格式
            ICellStyle style = wb.CreateCellStyle();
            style.WrapText = true;
            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;
            //选择表并根据行号、列号创建单元格并赋值
            ISheet sheet = wb.GetSheetAt(0);
            ICell cell = sheet.CreateRow(rowNum).CreateCell(cellNum);
            cell.CellStyle = style;
            cell.SetCellValue(value);
            //写入文件
            fs = new FileStream(bookName, FileMode.Open, FileAccess.Write);//配置文件流写入
            wb.Write(fs);//向表中写入数据
            fs.Close();//关闭文件
            wb.Close();//关闭工作簿

            return 0;
        }
        /// <summary>
        /// DataTable导出本地Excel
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="strPath">表格名称(绝对路径)</param>
        /// <returns>0:导出成功 -1：导出失败</returns>
        public static int ExportDataTableToExcel(DataTable dataSource, string strPath)
        {
            IWorkbook wb;

            if (!File.Exists(strPath))
            {
                return -1;
            }

            if (Path.GetExtension(strPath) == ".xls")
            {
                wb = new HSSFWorkbook();
            }
            else if (Path.GetExtension(strPath) == ".xlsx")
            {
                wb = new XSSFWorkbook();
            }
            else
            {
                return -1;
            }

            ISheet worksheet = wb.CreateSheet("Sheet1");
            //写入标题
            IRow TitleRow = worksheet.CreateRow(0);//创建标题行
            for (int i = 0; i < dataSource.Columns.Count; i++)
            {
                TitleRow.CreateCell(i).SetCellValue(dataSource.Columns[i].ToString());
            }
            //写入数据内容
            var row = 1;
            foreach (DataRow dataRow in dataSource.Rows)
            {
                IRow newRow = worksheet.CreateRow(row);//新增数据行
                //填充各数据列
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    newRow.CreateCell(i).SetCellValue(dataRow[i].ToString());
                }
                row++;//行数递增
            }
            //设置自动列宽
            for (int i = 0; i < dataSource.Columns.Count; i++)
            {
                worksheet.AutoSizeColumn(i);
            }
            //写入文件流
            using (FileStream fs = new FileStream(strPath, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }
            wb.Dispose();//释放资源

            return 0;
        }

        #endregion

        #region 异步方法
        /// <summary>
        /// 创建新的本地表格(异步)
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="sheetName">页名</param>
        /// <returns>创建的数据流</returns>
        public static async Task<FileStream>? CreateLocalExcelAsync(string bookName, string sheetName = "sheet1")
        {
            return await Task.Run(() => CreateLocalExcel(bookName, sheetName)!);
        }
        /// <summary>
        /// 获取表格中的sheet名称
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <returns></returns>
        public static async Task<string[]> GetSheetAsync(string path)
        {
            return await Task.Run(() => GetSheet(path));
        }
        /// <summary>
        /// 获取所选列的所有值
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <param name="sheetname">sheet名称</param>
        /// <param name="cellnum">列索引(从0开始)</param>
        /// <returns></returns>
        public static async Task<string[]> GetCellValuesAsync(string path, string sheetname, int cellnum)
        {
            return await Task.Run(() => GetCellValues(path, sheetname, cellnum));
        }
        /// <summary>
        /// 获取所选行的所有值
        /// </summary>
        /// <param name="path">本地表格路径</param>
        /// <param name="sheetname">sheet名称</param>
        /// <param name="rownum">行索引(从0开始)</param>
        /// <returns></returns>
        public static async Task<string[]> GetRowValuesAsync(string path, string sheetname, int rownum)
        {
            return await Task.Run(() => GetRowValues(path, sheetname, rownum));
        }
        /// <summary>
        /// 向表格中批量插入一行数据(带标题行)(异步)
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="cellTitleVlaue">标题行写入值</param>
        /// <param name="cellDataVlaue">数据行写入值</param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static async Task<int> InsertDataToLocalExcelAsync(string bookName, List<string> cellTitleVlaue, List<string> cellDataVlaue)
        {
            return await Task.Run(() => InsertDataToLocalExcel(bookName, cellTitleVlaue, cellDataVlaue));
        }
        /// <summary>
        /// 向表格中批量插入一行数据(不带标题行)(异步)
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="cellDataVlaue">数据行写入值</param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static async Task<int> InsertDataToLocalExcelAsync(string bookName, List<string> cellDataVlaue)
        {
            return await Task.Run(() => InsertDataToLocalExcel(bookName, cellDataVlaue));
        }
        /// <summary>
        /// 向表格单元格中插入数据(异步)
        /// </summary>
        /// <param name="bookName">表名</param>
        /// <param name="rowNum">单元格行号</param>
        /// <param name="cellNum">单元格列号</param>
        /// <param name="value">要写入的值</param>
        /// <returns>0:插入成功 -1：插入失败</returns>
        public static async Task<int> InsertDataToLocalExcelCellAsync(string bookName, int rowNum, int cellNum, string value)
        {
            return await Task.Run(() => InsertDataToLocalExcelCell(bookName, rowNum, cellNum, value));
        }
        /// <summary>
        /// DataTable导出本地Excel(异步)
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="strPath">表格名称(绝对路径)</param>
        /// <returns>0:导出成功 -1：导出失败</returns>
        public static async Task<int> ExportDataTableToExcelAsync(DataTable dataSource, string strPath)
        {
            return await Task.Run(() => ExportDataTableToExcel(dataSource, strPath));
        }

        #endregion


    }
}
